package com.hongdee.scm.repertory.service.impl;

import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.scm.common.service.impl.Common;
import com.hongdee.scm.repertory.dao.ITScmRepertoryDao;
import com.hongdee.scm.repertory.entity.TScmReperory;
import com.hongdee.scm.repertory.service.ITScmRepertoryService;
import com.hongdee.scm.stock.entity.TScmStock;
import net.sf.jsqlparser.expression.StringValue;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 名  称:TScmRepertoryServiceImpl
 * 类  型:class
 * 描  述:
 * 路  径:com.hongdee.scm.repertory.service.impl
 * 作  者: 术洪超
 * 日  期:2017-05-31 16:03
 */

@Service
public class TScmRepertoryServiceImpl implements ITScmRepertoryService {

    String tabName = "T_SCM_STOCK";
    String entityName = "TScmStock";

    @Autowired
    private ITScmRepertoryDao itScmRepertoryDao;


    @Override
    public Map info(String id, String uid) throws Exception {

        List infoId = new ArrayList();
        String sql = "select * from " + tabName + " where id = ? and company_id = ?";
        infoId.add(id);
        infoId.add(TokenUtil.getCompanyId(uid));
        return this.itScmRepertoryDao.info(sql, infoId.toArray());
    }

    /**
     *
     * @param ids
     * @param saleType
     * @param saleTypeReason
     * @param allocationStatus
     * @throws Exception
     */
    @Override
    public void update(String[] ids, String saleType, String saleTypeReason,String allocationStatus) throws Exception {

        Map params = new HashMap();
        for(String id:ids){
            //调拨状态
            String hql = "update TScmStock set allocationStatus=:allocationStatus where carId = :id ";
            params.put("allocationStatus", allocationStatus);
            params.put("id", id);
            this.itScmRepertoryDao.update(hql, params);
            //销售状态
            this.itScmRepertoryDao.executeSQL("update t_scm_car set sale_type = ?,sale_type_reason = ? where id  = ?",saleType,saleTypeReason,id);
        }
    }

    @Override
    public Map page(String start, String limit, String uid, Map data) throws Exception {
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select TIMESTAMPDIFF(DAY,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) as stockDay, s.warehouse_id, s.enter_type,  date_format(s.enter_date,'%Y-%m-%d') enter_date, s.vin,c.appearance_name," +
                " c.trim_name,w.warehouse_name,c.choose_name,c.car_model_year,c.finance_type,trim(c.car_series) car_series,c.car_model,c.configure, c.sale_type," +
                " if(c.sale_date is not null and c.sale_date <>'','不可售(已售)','可售') sale," +
                " c.sale_type_reason, c.provide_company_name,s.allocation_status,s.car_id, TIMESTAMPDIFF(DAY,IF(c.enter_warehouse_date_start is not null,c.enter_warehouse_date_start,s.enter_date)," +
                "DATE_FORMAT(NOW(), '%Y-%m-%d') ) AS allStockDay from " +
                tabName + " s left join t_scm_warehouse w on s.warehouse_id = w.id left join t_scm_car c on c.id = s.car_id " );

        Common common = new Common();
        Boolean hasWhere = false;
        if(data.get("companyId")==null||data.get("companyId").equals("")||((ArrayList)data.get("companyId")).size()==0){
            throw new Exception("请选择公司!");
        }
        if (data.get("companyId") != null && !StringUtils.isEmpty(data.get("companyId"))) {

            List<String> companyIdList = new ArrayList();
            if("8a1029015ac58d43015ac65d411e000d".equals(((List<String>)data.get("companyId")).get(0))||"0".equals(((List<String>)data.get("companyId")).get(0))){//集团级
                List<Map<String,Object>> comList = TokenUtil.getDataCompany(uid);
                if(comList.size()>0){
                    for (Map<String,Object> com:comList){
                        companyIdList.add(com.get("id").toString());
                    }
                }else{
                    throw new Exception("数据权限有误！");
                }

            }else{
                companyIdList = (List<String>) data.get("companyId");
            }

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" s.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");
            }
            //params.add(data.get("companyId"));
        }


        //入库时间
        if (data.get("enterWarehouseDateStart") != null && !data.get("enterWarehouseDateStart").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" date_format(s.enter_date,'%Y-%m-%d') >= ?");
            params.add(data.get("enterWarehouseDateStart"));
        }
        //入库时间
        if (data.get("enterWarehouseDateStop") != null && !data.get("enterWarehouseDateStop").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" date_format(s.enter_date,'%Y-%m-%d') <= ?");
            params.add(data.get("enterWarehouseDateStop"));
        }
        //所在仓库
        if (data.get("warehouse") != null && !data.get("warehouse").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.warehouse_id = ?");
            params.add(data.get("warehouse"));
        }
        //入库类型
        if (data.get("enterType") != null && !data.get("enterType").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.enter_type = ?");
            params.add(data.get("enterType"));
        }

        if (data.get("interiorId") != null && !data.get("interiorId").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.trim_name = ?");
            params.add(data.get("interiorId"));
        }

        if (data.get("appearanceId") != null && !data.get("appearanceId").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.appearance_name = ?");
            params.add(data.get("appearanceId"));
        }
       //vin
        if(data.get("vin")!=null&&!data.get("vin").toString().equals("")){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.vin like ?");
            params.add("%"+data.get("vin")+"%");
        }
        //加装
//        if (data.get("installName") != null && !data.get("installName").toString().equals("")) {
//            sql += " and trim_name = ?";
//            params.add(data.get("interiorName"));
//        }

        //车辆状态
        if (data.get("saleType") != null && !data.get("saleType").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.sale_type = ?");
            params.add(data.get("saleType"));
        }

        if (data.get("carSeriesCode") != null && !data.get("carSeriesCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.car_series_id = ?");
            params.add(data.get("carSeriesCode"));
        }
        //车辆模型
        if (data.get("carModelCode") != null && !data.get("carModelCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.car_model_id = ?");
            params.add(data.get("carModelCode"));
        }
        //车辆配置
        if (data.get("carConfigureCode") != null && !data.get("carConfigureCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.configure_id = ?");
            params.add(data.get("carConfigureCode"));
        }

        //财务状态
        if(data.get("financeType")!=null&&!data.get("financeType").toString().equals("")){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.finance_type = ?");
            params.add(data.get("financeType"));
        }
        if (data.get("enterWarehouseAge") != null && !data.get("enterWarehouseAge").toString().equals("")) {

            switch (data.get("enterWarehouseAge").toString()) {
                //入库一个月以内
                case "0": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) <1");
                }
                break;
                //入库一到三个月
                case "1": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 1<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) < 3");
                }
                break;
                //入库三到六个月
                case "2": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 3<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))<6");
                }
                break;
                //入库六到十二个月
                case "3": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 6<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))<12");
                }
                break;
                //入库十二个月以上
                case "4": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))>12");
                }
                break;
                default:
                break;
            }

        }
        sql.append(" order by date_format(s.enter_date, '%Y-%m-%d') desc");
        return this.itScmRepertoryDao.page(sql.toString(), params.toArray(), start, limit);
    }

    @Override
    public Map pageApp(String start, String limit, String uid, Map data) throws Exception{
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select TIMESTAMPDIFF(DAY,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) as stockDay, s.warehouse_id," +
                " case s.enter_type when '1' then '采购入库' when '2' then '销售退车入库' when '3' then'调拨入库' END enter_type, date_format(s.enter_date,'%Y-%m-%d') enter_date, c.vin,c.appearance_name," +
                " c.trim_name,w.warehouse_name,c.choose_name,c.car_model_year,case c.finance_type when '1' then '验车单已打印' when '2' then '暂估入库' when '3' then '正式入库' when '4' then '整车红冲车辆' end finance_type," +
                "c.car_series,c.car_model,c.configure, case c.sale_type when '1' then '可销售' when '2' then '不可销售' end sale_type," +
                " c.sale_type_reason, c.provide_company_name,case s.allocation_status when '0' then '可调拨' when '1' then '不可调拨' end allocation_status,s.car_id from " +
                tabName + " s left join t_scm_warehouse w on s.warehouse_id = w.id left join t_scm_car c on c.id = s.car_id " );

        Common common = new Common();
        Boolean hasWhere = false;

        String cid = TokenUtil.getCompanyId(uid);
        if(data.get("companyId")==null||data.get("companyId").equals("")||((ArrayList)data.get("companyId")).size()==0){
            throw new Exception("请选择公司!");
        }
        if (data.get("companyId") != null && !StringUtils.isEmpty(data.get("companyId"))) {

            List<String> companyIdList = new ArrayList();
            if("8a1029015ac58d43015ac65d411e000d".equals(((List<String>)data.get("companyId")).get(0))||"0".equals(((List<String>)data.get("companyId")).get(0))){//集团级
                List<Map<String,Object>> comList = TokenUtil.getDataCompany(uid);
                if(comList.size()>0){
                    for (Map<String,Object> com:comList){
                        companyIdList.add(com.get("id").toString());
                    }
                }else{
                    throw new Exception("数据权限有误！");
                }

            }else{
                companyIdList = (List<String>) data.get("companyId");
            }

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" s.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");
            }
            //params.add(data.get("companyId"));
        }
        //所在仓库
        if (data.get("warehouse") != null && !data.get("warehouse").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.warehouse_id in ( ");
            List<String> warehouseList = (ArrayList) data.get("warehouse");
            for (String warehouse : warehouseList) {
                sql.append(" ? , ");
                params.add(warehouse);
            }
            sql.append(" '' ) ");
        }
        //入库时间
        if (data.get("enterWarehouseDateStart") != null && !data.get("enterWarehouseDateStart").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" date_format(s.enter_date,'%Y-%m-%d') >= ?");
            params.add(data.get("enterWarehouseDateStart"));
        }
        //入库时间
        if (data.get("enterWarehouseDateStop") != null && !data.get("enterWarehouseDateStop").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" date_format(s.enter_date,'%Y-%m-%d') <= ?");
            params.add(data.get("enterWarehouseDateStop"));
        }
        //入库类型
        if (data.get("enterType") != null && !data.get("enterType").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.enter_type = ?");
            params.add(data.get("enterType"));
        }

        if (data.get("interiorId") != null && !data.get("interiorId").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.trim_name = ?");
            params.add(data.get("interiorId"));
        }

        if (data.get("appearanceId") != null && !data.get("appearanceId").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.appearance_name = ?");
            params.add(data.get("appearanceId"));
        }
        //vin
        if(data.get("vin")!=null&&!data.get("vin").toString().equals("")){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.vin like ?");
            params.add("%"+data.get("vin")+"%");
        }
        //加装
//        if (data.get("installName") != null && !data.get("installName").toString().equals("")) {
//            sql += " and trim_name = ?";
//            params.add(data.get("interiorName"));
//        }

        //车辆状态
        if (data.get("saleType") != null && !data.get("saleType").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.sale_type = ?");
            params.add(data.get("saleType"));
        }

        if (data.get("carSeriesCode") != null && !data.get("carSeriesCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.car_series_id = ?");
            params.add(data.get("carSeriesCode"));
        }
        //车辆模型
        if (data.get("carModelCode") != null && !data.get("carModelCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.car_model_id = ?");
            params.add(data.get("carModelCode"));
        }
        //车辆配置
        if (data.get("carConfigureCode") != null && !data.get("carConfigureCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.configure_id = ?");
            params.add(data.get("carConfigureCode"));
        }

        //财务状态
        if(data.get("financeType")!=null&&!data.get("financeType").toString().equals("")){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.finance_type = ?");
            params.add(data.get("financeType"));
        }
        if (data.get("enterWarehouseAge") != null && !data.get("enterWarehouseAge").toString().equals("")) {

            switch (data.get("enterWarehouseAge").toString()) {
                //入库一个月以内
                case "0": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) <1");
                }
                break;
                //入库一到三个月
                case "1": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 1<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) < 3");
                }
                break;
                //入库三到六个月
                case "2": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 3<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))<6");
                }
                break;
                //入库六到十二个月
                case "3": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 6<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))<12");
                }
                break;
                //入库十二个月以上
                case "4": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))>12");
                }
                break;
                default:
                break;
            }

        }
        sql.append(" order by date_format(s.enter_date, '%Y-%m-%d') desc");
        return this.itScmRepertoryDao.page(sql.toString(), params.toArray(), start, limit);
    }

    @Override
    public Map repertoryReport(String radio,String uid,Map data) throws Exception {

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();

        Common common = new Common();
        Boolean hasWhere = false;

        sql.append("select ");

        if (!StringUtils.isEmpty(radio)) {
            if ("1".equals(radio)) {
                sql.append(" count(1) as value, trim(c.car_series) as name ");
            }

            if ("2".equals(radio)) {
                sql.append(" count(1) as value, trim(c.car_model) as name ");
            }

            if ("3".equals(radio)) {
                sql.append(" count(1) as value, trim(c.configure) as name");
            }

        }

        sql.append(" from " + tabName+" s");

        sql.append(" left join t_scm_car c on c.id = s.car_id");
        String cid = TokenUtil.getCompanyId(uid);

        if (data.get("companyId") != null && !StringUtils.isEmpty(data.get("companyId"))) {

            List<String> companyIdList = new ArrayList();
            if("8a1029015ac58d43015ac65d411e000d".equals(((List<String>)data.get("companyId")).get(0))||"0".equals(((List<String>)data.get("companyId")).get(0))){//集团级
                List<Map<String,Object>> comList = TokenUtil.getDataCompany(uid);
                if(comList.size()>0){
                    for (Map<String,Object> com:comList){
                        companyIdList.add(com.get("id").toString());
                    }
                }else{
                    throw new Exception("数据权限有误！");
                }
            }else{
                companyIdList = (List<String>) data.get("companyId");
            }
            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" s.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");
            }
        }


        if (data.get("warehouseId")!=null&&!"".equals(data.get("warehouseId"))) {//仓库
            List<String> warehouseList = (ArrayList) data.get("warehouseId");

            if (data.get("warehouseId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" s.warehouse_id in ( ");
                for (String warehouseId : warehouseList) {
                    sql.append(" ? , ");
                    params.add(warehouseId);
                }
                sql.append(" '' ) ");
            }
        }

        if (!StringUtils.isEmpty(radio)) {
            if ("1".equals(radio)) {
                sql.append(" group by c.car_series");
            }

            if ("2".equals(radio)) {
                sql.append(" group by c.car_model ");
            }

            if ("3".equals(radio)) {
                sql.append(" group by c.configure ");
            }

        }
        List list = this.itScmRepertoryDao.list(sql.toString(), params.toArray());

        Map dataList = new HashMap();
        dataList.put("dataList", list);
        return dataList;
    }

    @Override
    public Map reportPage(String start, String limit, String uid, String name,String type,Map data) throws Exception {

        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        Common common = new Common();
        Boolean hasWhere = false;

        sql.append("SELECT s.vin,trim(c.car_series) car_series,trim(c.car_model) car_model,trim(c.configure) configure," +
                " TIMESTAMPDIFF( DAY,s.enter_date,DATE_FORMAT(NOW(), '%Y-%m-%d')) AS stockDay,w.warehouse_name" +
                " from t_scm_stock s LEFT JOIN t_scm_car c ON c.id = s.car_id" +
                " LEFT JOIN t_scm_warehouse w ON s.warehouse_id = w.id");

        String cid = TokenUtil.getCompanyId(uid);

        if (data.get("companyId") != null && !StringUtils.isEmpty(data.get("companyId"))) {

            List<String> companyIdList = new ArrayList();
            if("8a1029015ac58d43015ac65d411e000d".equals(((List<String>)data.get("companyId")).get(0))||"0".equals(((List<String>)data.get("companyId")).get(0))){//集团级
                List<Map<String,Object>> comList = TokenUtil.getDataCompany(uid);
                if(comList.size()>0){
                    for (Map<String,Object> com:comList){
                        companyIdList.add(com.get("id").toString());
                    }
                }else{
                    throw new Exception("数据权限有误！");
                }
            }else{
                companyIdList = (List<String>) data.get("companyId");
            }
            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" s.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");
            }
        }

        if (data.get("warehouseId")!=null&&!"".equals(data.get("warehouseId"))) {//仓库
            List<String> warehouseList = (ArrayList) data.get("warehouseId");

            if (data.get("warehouseId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" s.warehouse_id in ( ");
                for (String warehouseId : warehouseList) {
                    sql.append(" ? , ");
                    params.add(warehouseId);
                }
                sql.append(" '' ) ");
            }
        }

        if (!StringUtils.isEmpty(name)) {
            //车系
            if (type.equals("1")){
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append("c.car_series=?");
            }
            //车型
            if(type.equals("2")){
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append("c.car_model=?");
            }
            //配置
            if(type.equals("3")){
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append("c.configure = ?");
            }
            params.add(name);
        }

        sql.append(" order by s.create_time desc");
        return this.itScmRepertoryDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * 导出获取list
     * @param uid
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public List<TScmReperory> getExcel(String uid, Map data) throws Exception{

        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select TIMESTAMPDIFF(DAY,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) as stockDay, " +
                " case s.enter_type when '1' then '采购入库' when '2' then '销售退车入库' when '3' then'调拨入库' END enter_type, date_format(s.enter_date,'%Y-%m-%d') enter_date, c.vin,c.appearance_name," +
                " c.trim_name,if(w.warehouse_name is null,'',w.warehouse_name) warehouse_name,c.choose_name,c.car_model_year,case c.finance_type when '1' then '验车单已打印' when '2' then '暂估入库' when '3' then '正式入库' when '4' then '整车红冲车辆' end finance_type," +
                "c.car_series,c.car_model,c.configure,if(if(c.invoice_price is not null and c.invoice_price <> '',c.invoice_price,c.estimate_price) is null,0,if(c.invoice_price is not null and c.invoice_price <> '',c.invoice_price,c.estimate_price)) price, " +
                " c.provide_company_name," +
                "TIMESTAMPDIFF(DAY,IF(c.enter_warehouse_date_start is not null,c.enter_warehouse_date_start,s.enter_date)," +
                " DATE_FORMAT(NOW(), '%Y-%m-%d') ) AS allStockDay  from "+
                tabName + " s left join t_scm_warehouse w on s.warehouse_id = w.id left join t_scm_car c on c.id = s.car_id " );

        Common common = new Common();
        Boolean hasWhere = false;

        String cid = TokenUtil.getCompanyId(uid);
        if(data.get("companyId")==null||data.get("companyId").equals("")){
            throw new Exception("请选择公司!");
        }
        if (data.get("companyId") != null && !StringUtils.isEmpty(data.get("companyId"))) {

            List<String> companyIdList = new ArrayList();
            if("8a1029015ac58d43015ac65d411e000d".equals(((List<String>)data.get("companyId")).get(0))){//集团级
                List<Map<String,Object>> comList = TokenUtil.getDataCompany(uid);
                if(comList.size()>0){
                    for (Map<String,Object> com:comList){
                        companyIdList.add(com.get("id").toString());
                    }
                }else{
                    throw new Exception("数据权限有误！");
                }

            }else{
                companyIdList = (List<String>) data.get("companyId");
            }

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" s.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");
            }
            //params.add(data.get("companyId"));
        }
        //所在仓库
        if (data.get("warehouse") != null && !data.get("warehouse").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.warehouse_id in ( ");
            List<String> warehouseList = (ArrayList) data.get("warehouse");
            for (String warehouse : warehouseList) {
                sql.append(" ? , ");
                params.add(warehouse);
            }
            sql.append(" '' ) ");
        }
        //入库时间
        if (data.get("enterWarehouseDateStart") != null && !data.get("enterWarehouseDateStart").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" date_format(s.enter_date,'%Y-%m-%d') >= ?");
            params.add(data.get("enterWarehouseDateStart"));
        }
        //入库时间
        if (data.get("enterWarehouseDateStop") != null && !data.get("enterWarehouseDateStop").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" date_format(s.enter_date,'%Y-%m-%d') <= ?");
            params.add(data.get("enterWarehouseDateStop"));
        }
        //入库类型
        if (data.get("enterType") != null && !data.get("enterType").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.enter_type = ?");
            params.add(data.get("enterType"));
        }

        if (data.get("interiorId") != null && !data.get("interiorId").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.trim_name = ?");
            params.add(data.get("interiorId"));
        }

        if (data.get("appearanceId") != null && !data.get("appearanceId").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.appearance_name = ?");
            params.add(data.get("appearanceId"));
        }
        //vin
        if(data.get("vin")!=null&&!data.get("vin").toString().equals("")){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" s.vin like ?");
            params.add("%"+data.get("vin")+"%");
        }
        //加装
//        if (data.get("installName") != null && !data.get("installName").toString().equals("")) {
//            sql += " and trim_name = ?";
//            params.add(data.get("interiorName"));
//        }

        //车辆状态
        if (data.get("saleType") != null && !data.get("saleType").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.sale_type = ?");
            params.add(data.get("saleType"));
        }

        if (data.get("carSeriesCode") != null && !data.get("carSeriesCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.car_series_id = ?");
            params.add(data.get("carSeriesCode"));
        }
        //车辆模型
        if (data.get("carModelCode") != null && !data.get("carModelCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.car_model_id = ?");
            params.add(data.get("carModelCode"));
        }
        //车辆配置
        if (data.get("carConfigureCode") != null && !data.get("carConfigureCode").toString().equals("")) {
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.configure_id = ?");
            params.add(data.get("carConfigureCode"));
        }

        //财务状态
        if(data.get("financeType")!=null&&!data.get("financeType").toString().equals("")){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append("c.finance_type = ?");
            params.add(data.get("financeType"));
        }
        if (data.get("enterWarehouseAge") != null && !data.get("enterWarehouseAge").toString().equals("")) {

            switch (data.get("enterWarehouseAge").toString()) {
                //入库一个月以内
                case "0": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) <1");
                }
                break;
                //入库一到三个月
                case "1": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 1<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) < 3");
                }
                break;
                //入库三到六个月
                case "2": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 3<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))<6");
                }
                break;
                //入库六到十二个月
                case "3": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" 6<= TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d')) and TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))<12");
                }
                break;
                //入库十二个月以上
                case "4": {
                    hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                    sql.append(" TIMESTAMPDIFF(MONTH,s.enter_date,DATE_FORMAT(NOW(),'%Y-%m-%d'))>12");
                }
                break;
                default:
                    break;
            }

        }
        sql.append(" order by date_format(s.enter_date, '%Y-%m-%d') desc");
        List<Map <String,Object>> list=this.itScmRepertoryDao.list(sql.toString(), params.toArray());

        List<TScmReperory> tScmReperories = new ArrayList<>();
        for (Map map : list) {
            TScmReperory tScmReperory = new TScmReperory();
            tScmReperory.setVin(String.valueOf(map.get("vin")));
            tScmReperory.setCarSeries(String.valueOf(map.get("carSeries")));
            tScmReperory.setStockDates(String.valueOf(map.get("stockDay")));
            tScmReperory.setInType(String.valueOf(map.get("enterType")));
            tScmReperory.setInTime(String.valueOf(map.get("enterDate")));
            tScmReperory.setWarehouse(String.valueOf(map.get("warehouseName")));
            tScmReperory.setFinanceType(String.valueOf(map.get("financeType")));
            tScmReperory.setCarModel(String.valueOf(map.get("carModel")));
            tScmReperory.setCarConfigure(String.valueOf(map.get("configure")));
            tScmReperory.setStockAllDates(String.valueOf(map.get("allStockDay")));
            tScmReperory.setCostPrice(String.valueOf(map.get("price")));
            tScmReperories.add(tScmReperory);
        }
        return tScmReperories;
    }

    @Override
    public XSSFWorkbook createExcel(List<TScmReperory> list, String uid) throws Exception{
        String[] excelHeaderRecordout = {"车系名称","车型名称","配置名称","VIN","所在仓库","财务状态","入库类型","入库时间", "库存天数", "总库存天数","成本价"};

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("库存明细");
        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmReperory tScmReperory = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmReperory.getCarSeries());
            row.createCell(1).setCellValue(tScmReperory.getCarModel());
            row.createCell(2).setCellValue(tScmReperory.getCarConfigure());
            row.createCell(3).setCellValue(tScmReperory.getVin());
            row.createCell(4).setCellValue(tScmReperory.getWarehouse());
            row.createCell(5).setCellValue(tScmReperory.getFinanceType());
            row.createCell(6).setCellValue(tScmReperory.getInType());
            row.createCell(7).setCellValue(tScmReperory.getInTime());
            row.createCell(8).setCellValue(tScmReperory.getStockDates());
            row.createCell(9).setCellValue(tScmReperory.getStockAllDates());
            row.createCell(10).setCellValue(tScmReperory.getCostPrice());
        }
        return wb;
    }

}
